<!DOCTYPE html>
<html>
    <head>
        <title>Pick-up Game</title>
        <link rel="stylesheet" href="style.css" type="text/css" />
        <script type="text/javascript" src="index.js"></script>
    </head>

    <body>

        <?php
        include_once('menu.php');
        if (!empty($_SESSION['username'])) :

            // connect to database
            $conn_str = 'host=dbsrv1.cdf.toronto.edu dbname=csc309g23 user=csc309g23 password=ipheid5h';
            $conn = pg_connect($conn_str);

            // query database to find games
            $query = 'SELECT * FROM games WHERE gameid=' . $_GET['gameid'];
            $result = pg_query($conn, $query);
            $row = pg_fetch_assoc($result);
			$sport = $row['sport'];
			$end_time = $row['endtime'];
            // query database to find the organizer of the game
            $query = 'SELECT username FROM rosters WHERE gameid=' . $_GET['gameid'] . 'AND organizer=\'t\'';
            $result = pg_query($conn, $query);
            $organizer = pg_fetch_row($result);
	    if (($_SESSION['username']===$organizer[0])){
		$is_organizer = TRUE;
	    } else {
		$is_organizer = FALSE;
	    }
	    $query2 = 'SELECT playstatus FROM games WHERE gameid='. $_GET['gameid'];
	    $result2 = pg_query($conn, $query2);
	    $playstatus = pg_fetch_row($result2);
	    $p_status = $playstatus[0];
	    $query3 = 'SELECT endtime > current_timestamp FROM games where gameid='.$_GET['gameid'];
	    $result3 = pg_query($conn, $query3);
	    $timecheck = pg_fetch_row($result3);
	    $t_status = $timecheck[0];
            ?>
	
            
        <div class="container">
	    <?php if (($p_status==NULL) && ($is_organizer==TRUE) && ($t_status=="f")) {
		    ?>
		    <h2> Play Status </h2>
		    <hr>
		    <form action="confirmplayed.php" method="post">
			<select name="pstatus"><option value="played">Game has been played</option><option value="missed">Game Cancelled</option></select>
			<input type='hidden' name='gameid' value =<?php echo $_GET['gameid'] ?> />
			<input type='submit' name='submit' value = 'confirm'/>
		    </form>
		    <?php
		}elseif($p_status==NULL){
		    ?>
		    <h2> Play Status </h2>
		    <hr>
		    <p> This game has yet to be confirmed as played by the Organizer. </p>
		    <?php
		}elseif($p_status=="t"){
		    ?>
		    <h2> Play Status </h2>
		    <hr>
		    <p> This game has been confirmed as played by the organizer and is now open to voting </p>
		    <?php
		}elseif($p_status=="f"){
		    ?>
		    <h2> Play Status </h2>
		    <hr>
		    <p> This game has been confirmed as CANCELLED by the organizer.</p>
		    <?php
		}
	    ?>
            <h2>Game Details</h2>
            <hr></hr>
                <table class="vTable">
                    <tr><td>Organizer</td>
						<td><?= $organizer[0] ?></td>  
						<td> <?php 
								$org_rating_query = 'SELECT SUM(organizing) as total, COUNT(*) as count FROM rating WHERE player=\'' . $organizer[0] . 
											'\' AND sport=\'' . $sport . '\' AND organizing is not NULL';
								
								$org_rating_result = pg_query($conn, $org_rating_query);	
								$org_rating = pg_fetch_assoc($org_rating_result);
								if ($org_rating['count'] != 0) {									  
								  echo 'rate ' . $org_rating['total'] / $org_rating['count'] . '/5 of ' . $org_rating['count'] . ' votes';										  
								} else {  
								  echo 'rate 0/5 of 0 votes'; } ?>
						</td>
						<td>
								<?php 
								$org_voted_query = 'SELECT voter, player, gameid, organizing FROM rating WHERE voter=\'' .
											$_SESSION['username'] . 
											'\' AND player=\'' .$organizer[0] . 
											'\' AND gameid=' .  $_GET['gameid'] . ' AND organizing is not NULL'; 
								$org_voted = pg_query($conn, $org_voted_query); 
								
								if (($end_time < date("Y-m-d H:i:s")) && ($organizer[0] != $_SESSION['username'])&&($p_status=="t")) { ?> 
								  <form action="vote.php" method="post">
									<select name="rating"> 
									  <option>0</option>
									  <option>1</option>
									  <option>2</option>
									  <option>3</option>
									  <option>4</option>
									  <option>5</option>
									</select>
									<input type="hidden" name="organizer" value=1 ></input>	
									<input type="hidden" name="voter" value=<?= $_SESSION['username'] ?>  ></input>
									<input type="hidden" name="player" value=<?= $organizer[0] ?> ></input>
									<input type="hidden" name="gameid" value=<?= $_GET['gameid'] ?> ></input>
									<input type="hidden" name="sport" value=<?= $sport ?> ></input>									
									<?php if (pg_num_rows($org_voted) == 0) { ?>
										  <input type="hidden" name="revote" value=0></input>
										  <input type="submit" value="Vote"></input>										  
									<?php } else { ?>
										  <input type="hidden" name="revote" value=1></input>
										  <input type="submit" value="Revote"></input> <?php } ?>										  
								  </form>
								  <?php } ?>
							  </td>
				
                    </tr>
                    <tr><td>Skill</td><td><?= $row['skill'] ?></td></tr>
                    <tr><td>Sport</td><td><?= $row['sport'] ?></td></tr>
                    <tr><td>Address</td><td><?= $row['address'] ?></td></tr>
                    <tr><td>City</td><td><?= $row['city'] ?></td></tr>
                    <tr><td>Country</td><td><?= $row['country'] ?></td></tr>
                    <tr><td>Start</td><td><?= $row['start'] ?></td></tr>
                    <tr><td>End</td><td><?= $row['endtime'] ?></td></tr>
                </table>

            <h2>Team A</h2>
                <hr></hr>	  
				
					  <table id="game_details_with_vote" width=500>
					  <?php
					  
					  
					  // query database to find all members on team A
					  $query = 'SELECT * FROM rosters WHERE gameid=' . $_GET['gameid'] . ' AND team=0 AND status IN (2,3,4)';
					  $result = pg_query($conn, $query);
					  
					  while ($row = pg_fetch_assoc($result)) {

						  $sub_query = 'SELECT * FROM members WHERE username=\'' . $row['username'] . '\'';
						  $rating_query = 'SELECT SUM(playing) as total, COUNT(*) as count FROM rating WHERE player=\'' . $row['username'] . 
											'\' AND sport=\'' . $sport . '\' AND playing is NOT NULL';
						  $sub_result = pg_query($conn, $sub_query);
						  $rating_result = pg_query($conn, $rating_query);							
						  
						  $rating = pg_fetch_assoc($rating_result);						  
						  $player = pg_fetch_assoc($sub_result); ?>
						  
						  <tr><td><?= $player['firstname'] . ' ' . $player['lastname'] ?></td>	
							  <td><?php 
								if ($rating['count'] != 0) {									  
								  echo 'rate ' . $rating['total'] / $rating['count'] . '/5 of ' . $rating['count'] . ' votes';										  
								} else {  
								  echo 'rate 0/5 of 0 votes'; } ?>
							  </td>	
							  <td>
								<?php 
								$voted_query = 'SELECT voter, player, gameid, playing FROM rating WHERE voter=\'' .
											$_SESSION['username'] . '\' AND player=\'' .
											$row['username'] . '\' AND gameid=' .
											$_GET['gameid'] . ' AND playing is not NULL'; 
								$voted = pg_query($conn, $voted_query); 
								
								if ($end_time < date("Y-m-d H:i:s") && $row['username'] != $_SESSION['username'] && $p_status=="t") { ?> 
								  <form action="vote.php" method="post">
									<select name="rating">
									  <option value=-1>-1 (Did not show)</option>
									  <option>0</option>
									  <option>1</option>
									  <option>2</option>
									  <option>3</option>
									  <option>4</option>
									  <option>5</option>
									</select>
									<input type="hidden" name="organizer" value=0 ></input>	
									<input type="hidden" name="voter" value=<?= $_SESSION['username'] ?>  ></input>
									<input type="hidden" name="player" value=<?= $row['username'] ?> ></input>
									<input type="hidden" name="gameid" value=<?= $_GET['gameid'] ?> ></input>
									<input type="hidden" name="sport" value=<?= $sport ?> ></input>									
									<?php if (pg_num_rows($voted) == 0) { ?>
										  <input type="hidden" name="revote" value=0></input>
										  <input type="submit" value="Vote"></input>										  
									<?php } else { ?>
										  <input type="hidden" name="revote" value=1></input>
										  <input type="submit" value="Revote"></input> <?php } ?>
										  
								  </form>
								  <?php } ?>
							  </td>
							</tr> 
							
					  <?php } ?>
					</table>
                
                <h2>Team B</h2>
			  
                <hr></hr>
                <table id="game_details_with_vote" width=500>
					  <?php
					  
					  
					  // query database to find all members on team A
					  $query = 'SELECT * FROM rosters WHERE gameid=' . $_GET['gameid'] . ' AND team=1 AND status IN (2,3,4)';
					  $result = pg_query($conn, $query);
					  
					  while ($row = pg_fetch_assoc($result)) {

						  $sub_query = 'SELECT * FROM members WHERE username=\'' . $row['username'] . '\'';
						  $rating_query = 'SELECT SUM(playing) as total, COUNT(*) as count FROM rating WHERE player=\'' . $row['username'] . 
											'\' AND sport=\'' . $sport . '\' AND playing is not NULL';
						  $sub_result = pg_query($conn, $sub_query);
						  $rating_result = pg_query($conn, $rating_query);							
						  
						  $rating = pg_fetch_assoc($rating_result);						  
						  $player = pg_fetch_assoc($sub_result); ?>
						  
						  <tr><td><?= $player['firstname'] . ' ' . $player['lastname'] ?></td>	
							  <td><?php 
								if ($rating['count'] != 0) {									  
								  echo 'rate ' . $rating['total'] / $rating['count'] . '/5 of ' . $rating['count'] . ' votes';										  
								} else {  
								  echo 'rate 0/5 of 0 votes'; } ?>
							  </td>	
							  <td>
								<?php 
								$voted_query = 'SELECT voter, player, gameid, playing FROM rating WHERE voter=\'' .
											$_SESSION['username'] . '\' AND player=\'' .
											$row['username'] . '\' AND gameid=' .
											$_GET['gameid'] . ' AND playing is not NULL'; 
								$voted = pg_query($conn, $voted_query); 
								
								if ($end_time < date("Y-m-d H:i:s") && $row['username'] != $_SESSION['username'] && $p_status=="t") { ?> 
								  <form action="vote.php" method="post">
									<select name="rating">
									  <option value=-1>-1 (Did not show)</option>
									  <option>0</option>
									  <option>1</option>
									  <option>2</option>
									  <option>3</option>
									  <option>4</option>
									  <option>5</option>
									</select>
									<input type="hidden" name="organizer" value=0 ></input>	
									<input type="hidden" name="voter" value=<?= $_SESSION['username'] ?>  ></input>
									<input type="hidden" name="player" value=<?= $row['username'] ?> ></input>
									<input type="hidden" name="gameid" value=<?= $_GET['gameid'] ?> ></input>
									<input type="hidden" name="sport" value=<?= $sport ?> ></input>									
									<?php if (pg_num_rows($voted) == 0) { ?>
										  <input type="hidden" name="revote" value=0></input>
										  <input type="submit" value="Vote"></input>										  
									<?php } else { ?>
										  <input type="hidden" name="revote" value=1></input>
										  <input type="submit" value="Revote"></input> <?php } ?>
										  
								  </form>
								  <?php } ?>
							  </td>
							</tr> 
							
					  <?php } ?>
					</table>    
                
                
                
            <?php  // accept invites and requests
            $query = 'SELECT * FROM rosters WHERE gameid=' . $_GET['gameid'] . ' AND username=\'' . $_SESSION['username'] . '\'';
            $result = pg_query($conn, $query);
            $row = pg_fetch_assoc($result);
            $status = $row['status'];
            
            if (empty($row)) { // if the user is not on the roster for a game ?>
		      <h2>Join Game</h2>
		      <hr></hr>
		      <form action="joinGame.php" method="post">
			  <select name="team">
			    <option value=2>Any</option>
			    <option value=0>Team A</option>
			    <option value=1>Team B</option>
			  </select>
			  <input type="hidden" value="<?= $_GET['gameid']?>" name="gameid">
			  <input type="submit" value="Send Request">
		      </form>
            <?php }
            
            if ($status == 3) { ?>
                <h2>Requests</h2>
                <hr></hr>
                    <table class="displayTable">
		      </tr><td>Player</td><td>Team</td><td>Action</td></tr>
                        <?php
                        // query database to find all game requests
                        $query = 'SELECT * FROM rosters
			   WHERE gameid=' . $_GET['gameid'] . ' AND status=0';
                        $result = pg_query($conn, $query);

                        while ($row = pg_fetch_assoc($result)) {

                            $sub_query = 'SELECT * FROM members WHERE username=\'' . $row['username'] . '\'';
                            $sub_result = pg_query($conn, $sub_query);
                            $player = pg_fetch_assoc($sub_result);
                            ?>
                            <tr><form action="acceptRequest.php" method="post">
				<td><?= $row['username'] ?></td>
				<td>
		      <?php
			  if ($row['team'] == 0) {
			    echo 'Team A'; ?>
			    <input type="hidden" value=<?= $row['team'] ?> name="team" >
			 <?php } else if ($row['team'] == 1) {
			    echo 'Team B'; ?>
			    <input type="hidden" value=<?= $row['team'] ?> name="team">
			 <?php } else { ?>
			  <select name="team">
			    <option value=0>Team A</option>
			    <option value=1>Team B</option>
			  </select>
		      <?php } ?>
		      </td>
				<td>
				    <input type="submit" value="Accept Request">
				    <input type="hidden" value=<?= $_GET['gameid']?> name="gameid">
				    <input type="hidden" value="<?= $row['username']?>" name="username">
				</td>
                            </form></tr>
			  
                        <?php } ?>
                    </table>
            <?php }  else if ($status == 1) { // if player is invited to join a game?>
                <h2>Invited</h2>
                <hr></hr>
                <table>
		  <tr>
			  
		    <td>
		      <?php
			  if ($row['team'] == 0) {
			    echo 'Team A';
			  } else {
			    echo 'Team B';
			  }
		      ?>
		    </td>
		    <td>
		      <form action="acceptInvite.php" method="post">
			  <input type="hidden" value="<?= $_GET['gameid']?>" name="gameid">
			  <input type="submit" value="Accept Invitation">
		      </form>
		    <td>
		<tr>
                </table>
	    <?php } ?>
            <?php
        else :
            header('Location: /');
        endif;
        ?>
    </div>
    </body>
</html>